
DROP PROCEDURE IF EXISTS candidate_filter_lang;

DELIMITER //
CREATE PROCEDURE `candidate_filter_lang` (
    IN lang INT, IN langImp INT, IN langReadImp INT, IN langSpeakImp INT, IN langListenImp INT, IN langWriteImp INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER

BEGIN
    
    -- MAXIMUM SCORE IS 400 AND MINIMUM SCORE IS 0
    IF langImp = 10 THEN
        SELECT
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            (canLang.read_competency * langReadImp + canLang.speak_competency * langSpeakImp +
            canLang.listen_competency * langListenImp + canLang.write_competency * langWriteImp) AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_language AS canLang
        WHERE
            can.is_deleted = 0
            AND canLang.candidate_id = can.id
            AND canLang.language_id = lang;
    ELSE
        SELECT
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            (canLang.read_competency * langReadImp + canLang.speak_competency * langSpeakImp +
            canLang.listen_competency * langListenImp + canLang.write_competency * langWriteImp) * langImp / 10 AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_language AS canLang
        WHERE
            can.is_deleted = 0
            AND canLang.candidate_id = can.id
            AND canLang.language_id = lang;
    END IF;
END//

